{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window LAG\n",
    "\n",
    "## COVID-19 Data\n",
    "Notes on the data: This data was assembled based on work done by [Rodrigo Pombo](https://github.com/pomber/covid19) based on [John Hopkins University](https://systems.jhu.edu/research/public-health/ncov/), based on [World Health Organisation](https://www.who.int/health-topics/coronavirus). The data was assembled 21st April 2020 - there are no plans to keep this data set up to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Window Function\n",
    "The SQL Window functions include LAG, LEAD, RANK and NTILE. These functions operate over a \"window\" of rows - typically these are rows in the table that are in some sense adjacent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "covid = pd.read_sql_table('covid', engine)\n",
    "world = pd.read_sql_table('world', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `covid` table\n",
    "\n",
    "The example uses a WHERE clause to show the cases in 'Italy' in March.\n",
    "\n",
    "**Modify the query to show data from Spain**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13719</th>\n",
       "      <td>Spain</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13720</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13721</th>\n",
       "      <td>Spain</td>\n",
       "      <td>3</td>\n",
       "      <td>165</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13722</th>\n",
       "      <td>Spain</td>\n",
       "      <td>4</td>\n",
       "      <td>222</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13723</th>\n",
       "      <td>Spain</td>\n",
       "      <td>5</td>\n",
       "      <td>259</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13724</th>\n",
       "      <td>Spain</td>\n",
       "      <td>6</td>\n",
       "      <td>400</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13725</th>\n",
       "      <td>Spain</td>\n",
       "      <td>7</td>\n",
       "      <td>500</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13726</th>\n",
       "      <td>Spain</td>\n",
       "      <td>8</td>\n",
       "      <td>673</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13727</th>\n",
       "      <td>Spain</td>\n",
       "      <td>9</td>\n",
       "      <td>1073</td>\n",
       "      <td>28</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13728</th>\n",
       "      <td>Spain</td>\n",
       "      <td>10</td>\n",
       "      <td>1695</td>\n",
       "      <td>35</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13729</th>\n",
       "      <td>Spain</td>\n",
       "      <td>11</td>\n",
       "      <td>2277</td>\n",
       "      <td>54</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13730</th>\n",
       "      <td>Spain</td>\n",
       "      <td>12</td>\n",
       "      <td>2277</td>\n",
       "      <td>55</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13731</th>\n",
       "      <td>Spain</td>\n",
       "      <td>13</td>\n",
       "      <td>5232</td>\n",
       "      <td>133</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13732</th>\n",
       "      <td>Spain</td>\n",
       "      <td>14</td>\n",
       "      <td>6391</td>\n",
       "      <td>195</td>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13733</th>\n",
       "      <td>Spain</td>\n",
       "      <td>15</td>\n",
       "      <td>7798</td>\n",
       "      <td>289</td>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13734</th>\n",
       "      <td>Spain</td>\n",
       "      <td>16</td>\n",
       "      <td>9942</td>\n",
       "      <td>342</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13735</th>\n",
       "      <td>Spain</td>\n",
       "      <td>17</td>\n",
       "      <td>11748</td>\n",
       "      <td>533</td>\n",
       "      <td>1028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13736</th>\n",
       "      <td>Spain</td>\n",
       "      <td>18</td>\n",
       "      <td>13910</td>\n",
       "      <td>623</td>\n",
       "      <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13737</th>\n",
       "      <td>Spain</td>\n",
       "      <td>19</td>\n",
       "      <td>17963</td>\n",
       "      <td>830</td>\n",
       "      <td>1107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13738</th>\n",
       "      <td>Spain</td>\n",
       "      <td>20</td>\n",
       "      <td>20410</td>\n",
       "      <td>1043</td>\n",
       "      <td>1588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13739</th>\n",
       "      <td>Spain</td>\n",
       "      <td>21</td>\n",
       "      <td>25374</td>\n",
       "      <td>1375</td>\n",
       "      <td>2125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13740</th>\n",
       "      <td>Spain</td>\n",
       "      <td>22</td>\n",
       "      <td>28768</td>\n",
       "      <td>1772</td>\n",
       "      <td>2575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13741</th>\n",
       "      <td>Spain</td>\n",
       "      <td>23</td>\n",
       "      <td>35136</td>\n",
       "      <td>2311</td>\n",
       "      <td>2575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13742</th>\n",
       "      <td>Spain</td>\n",
       "      <td>24</td>\n",
       "      <td>39885</td>\n",
       "      <td>2808</td>\n",
       "      <td>3794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13743</th>\n",
       "      <td>Spain</td>\n",
       "      <td>25</td>\n",
       "      <td>49515</td>\n",
       "      <td>3647</td>\n",
       "      <td>5367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13744</th>\n",
       "      <td>Spain</td>\n",
       "      <td>26</td>\n",
       "      <td>57786</td>\n",
       "      <td>4365</td>\n",
       "      <td>7015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13745</th>\n",
       "      <td>Spain</td>\n",
       "      <td>27</td>\n",
       "      <td>65719</td>\n",
       "      <td>5138</td>\n",
       "      <td>9357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13746</th>\n",
       "      <td>Spain</td>\n",
       "      <td>28</td>\n",
       "      <td>73235</td>\n",
       "      <td>5982</td>\n",
       "      <td>12285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13747</th>\n",
       "      <td>Spain</td>\n",
       "      <td>29</td>\n",
       "      <td>80110</td>\n",
       "      <td>6803</td>\n",
       "      <td>14709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13748</th>\n",
       "      <td>Spain</td>\n",
       "      <td>30</td>\n",
       "      <td>87956</td>\n",
       "      <td>7716</td>\n",
       "      <td>16780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13749</th>\n",
       "      <td>Spain</td>\n",
       "      <td>31</td>\n",
       "      <td>95923</td>\n",
       "      <td>8464</td>\n",
       "      <td>19259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name  day  confirmed  deaths  recovered\n",
       "13719  Spain    1         84       0          2\n",
       "13720  Spain    2        120       0          2\n",
       "13721  Spain    3        165       1          2\n",
       "13722  Spain    4        222       2          2\n",
       "13723  Spain    5        259       3          2\n",
       "13724  Spain    6        400       5          2\n",
       "13725  Spain    7        500      10         30\n",
       "13726  Spain    8        673      17         30\n",
       "13727  Spain    9       1073      28         32\n",
       "13728  Spain   10       1695      35         32\n",
       "13729  Spain   11       2277      54        183\n",
       "13730  Spain   12       2277      55        183\n",
       "13731  Spain   13       5232     133        193\n",
       "13732  Spain   14       6391     195        517\n",
       "13733  Spain   15       7798     289        517\n",
       "13734  Spain   16       9942     342        530\n",
       "13735  Spain   17      11748     533       1028\n",
       "13736  Spain   18      13910     623       1081\n",
       "13737  Spain   19      17963     830       1107\n",
       "13738  Spain   20      20410    1043       1588\n",
       "13739  Spain   21      25374    1375       2125\n",
       "13740  Spain   22      28768    1772       2575\n",
       "13741  Spain   23      35136    2311       2575\n",
       "13742  Spain   24      39885    2808       3794\n",
       "13743  Spain   25      49515    3647       5367\n",
       "13744  Spain   26      57786    4365       7015\n",
       "13745  Spain   27      65719    5138       9357\n",
       "13746  Spain   28      73235    5982      12285\n",
       "13747  Spain   29      80110    6803      14709\n",
       "13748  Spain   30      87956    7716      16780\n",
       "13749  Spain   31      95923    8464      19259"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(covid.assign(day=covid['whn'].dt.day)\n",
    "    .loc[(covid['name']=='Spain') & (covid['whn'].dt.month==3),\n",
    "        ['name', 'day', 'confirmed', 'deaths', 'recovered']]\n",
    "    .sort_values('day'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introducing the LAG function\n",
    "\n",
    "The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.\n",
    "\n",
    "**Modify the query to show confirmed for the day before.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>mo</th>\n",
       "      <th>lag_cfrm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7329</th>\n",
       "      <td>Italy</td>\n",
       "      <td>1</td>\n",
       "      <td>1694</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7330</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2</td>\n",
       "      <td>2036</td>\n",
       "      <td>3</td>\n",
       "      <td>1694.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7331</th>\n",
       "      <td>Italy</td>\n",
       "      <td>3</td>\n",
       "      <td>2502</td>\n",
       "      <td>3</td>\n",
       "      <td>2036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7332</th>\n",
       "      <td>Italy</td>\n",
       "      <td>4</td>\n",
       "      <td>3089</td>\n",
       "      <td>3</td>\n",
       "      <td>2502.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7333</th>\n",
       "      <td>Italy</td>\n",
       "      <td>5</td>\n",
       "      <td>3858</td>\n",
       "      <td>3</td>\n",
       "      <td>3089.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7334</th>\n",
       "      <td>Italy</td>\n",
       "      <td>6</td>\n",
       "      <td>4636</td>\n",
       "      <td>3</td>\n",
       "      <td>3858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7335</th>\n",
       "      <td>Italy</td>\n",
       "      <td>7</td>\n",
       "      <td>5883</td>\n",
       "      <td>3</td>\n",
       "      <td>4636.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7336</th>\n",
       "      <td>Italy</td>\n",
       "      <td>8</td>\n",
       "      <td>7375</td>\n",
       "      <td>3</td>\n",
       "      <td>5883.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7337</th>\n",
       "      <td>Italy</td>\n",
       "      <td>9</td>\n",
       "      <td>9172</td>\n",
       "      <td>3</td>\n",
       "      <td>7375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7338</th>\n",
       "      <td>Italy</td>\n",
       "      <td>10</td>\n",
       "      <td>10149</td>\n",
       "      <td>3</td>\n",
       "      <td>9172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7339</th>\n",
       "      <td>Italy</td>\n",
       "      <td>11</td>\n",
       "      <td>12462</td>\n",
       "      <td>3</td>\n",
       "      <td>10149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7340</th>\n",
       "      <td>Italy</td>\n",
       "      <td>12</td>\n",
       "      <td>12462</td>\n",
       "      <td>3</td>\n",
       "      <td>12462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7341</th>\n",
       "      <td>Italy</td>\n",
       "      <td>13</td>\n",
       "      <td>17660</td>\n",
       "      <td>3</td>\n",
       "      <td>12462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7342</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14</td>\n",
       "      <td>21157</td>\n",
       "      <td>3</td>\n",
       "      <td>17660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7343</th>\n",
       "      <td>Italy</td>\n",
       "      <td>15</td>\n",
       "      <td>24747</td>\n",
       "      <td>3</td>\n",
       "      <td>21157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7344</th>\n",
       "      <td>Italy</td>\n",
       "      <td>16</td>\n",
       "      <td>27980</td>\n",
       "      <td>3</td>\n",
       "      <td>24747.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7345</th>\n",
       "      <td>Italy</td>\n",
       "      <td>17</td>\n",
       "      <td>31506</td>\n",
       "      <td>3</td>\n",
       "      <td>27980.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7346</th>\n",
       "      <td>Italy</td>\n",
       "      <td>18</td>\n",
       "      <td>35713</td>\n",
       "      <td>3</td>\n",
       "      <td>31506.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7347</th>\n",
       "      <td>Italy</td>\n",
       "      <td>19</td>\n",
       "      <td>41035</td>\n",
       "      <td>3</td>\n",
       "      <td>35713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7348</th>\n",
       "      <td>Italy</td>\n",
       "      <td>20</td>\n",
       "      <td>47021</td>\n",
       "      <td>3</td>\n",
       "      <td>41035.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7349</th>\n",
       "      <td>Italy</td>\n",
       "      <td>21</td>\n",
       "      <td>53578</td>\n",
       "      <td>3</td>\n",
       "      <td>47021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7350</th>\n",
       "      <td>Italy</td>\n",
       "      <td>22</td>\n",
       "      <td>59138</td>\n",
       "      <td>3</td>\n",
       "      <td>53578.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7351</th>\n",
       "      <td>Italy</td>\n",
       "      <td>23</td>\n",
       "      <td>63927</td>\n",
       "      <td>3</td>\n",
       "      <td>59138.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7352</th>\n",
       "      <td>Italy</td>\n",
       "      <td>24</td>\n",
       "      <td>69176</td>\n",
       "      <td>3</td>\n",
       "      <td>63927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7353</th>\n",
       "      <td>Italy</td>\n",
       "      <td>25</td>\n",
       "      <td>74386</td>\n",
       "      <td>3</td>\n",
       "      <td>69176.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7354</th>\n",
       "      <td>Italy</td>\n",
       "      <td>26</td>\n",
       "      <td>80589</td>\n",
       "      <td>3</td>\n",
       "      <td>74386.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7355</th>\n",
       "      <td>Italy</td>\n",
       "      <td>27</td>\n",
       "      <td>86498</td>\n",
       "      <td>3</td>\n",
       "      <td>80589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7356</th>\n",
       "      <td>Italy</td>\n",
       "      <td>28</td>\n",
       "      <td>92472</td>\n",
       "      <td>3</td>\n",
       "      <td>86498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7357</th>\n",
       "      <td>Italy</td>\n",
       "      <td>29</td>\n",
       "      <td>97689</td>\n",
       "      <td>3</td>\n",
       "      <td>92472.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7358</th>\n",
       "      <td>Italy</td>\n",
       "      <td>30</td>\n",
       "      <td>101739</td>\n",
       "      <td>3</td>\n",
       "      <td>97689.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7359</th>\n",
       "      <td>Italy</td>\n",
       "      <td>31</td>\n",
       "      <td>105792</td>\n",
       "      <td>3</td>\n",
       "      <td>101739.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       name  day  confirmed  mo  lag_cfrm\n",
       "7329  Italy    1       1694   3       NaN\n",
       "7330  Italy    2       2036   3    1694.0\n",
       "7331  Italy    3       2502   3    2036.0\n",
       "7332  Italy    4       3089   3    2502.0\n",
       "7333  Italy    5       3858   3    3089.0\n",
       "7334  Italy    6       4636   3    3858.0\n",
       "7335  Italy    7       5883   3    4636.0\n",
       "7336  Italy    8       7375   3    5883.0\n",
       "7337  Italy    9       9172   3    7375.0\n",
       "7338  Italy   10      10149   3    9172.0\n",
       "7339  Italy   11      12462   3   10149.0\n",
       "7340  Italy   12      12462   3   12462.0\n",
       "7341  Italy   13      17660   3   12462.0\n",
       "7342  Italy   14      21157   3   17660.0\n",
       "7343  Italy   15      24747   3   21157.0\n",
       "7344  Italy   16      27980   3   24747.0\n",
       "7345  Italy   17      31506   3   27980.0\n",
       "7346  Italy   18      35713   3   31506.0\n",
       "7347  Italy   19      41035   3   35713.0\n",
       "7348  Italy   20      47021   3   41035.0\n",
       "7349  Italy   21      53578   3   47021.0\n",
       "7350  Italy   22      59138   3   53578.0\n",
       "7351  Italy   23      63927   3   59138.0\n",
       "7352  Italy   24      69176   3   63927.0\n",
       "7353  Italy   25      74386   3   69176.0\n",
       "7354  Italy   26      80589   3   74386.0\n",
       "7355  Italy   27      86498   3   80589.0\n",
       "7356  Italy   28      92472   3   86498.0\n",
       "7357  Italy   29      97689   3   92472.0\n",
       "7358  Italy   30     101739   3   97689.0\n",
       "7359  Italy   31     105792   3  101739.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (covid.assign(day=covid['whn'].dt.day, mo=covid['whn'].dt.month)\n",
    "    .loc[(covid['name']=='Italy') & (covid['whn'].dt.month==3),\n",
    "        ['name', 'day', 'confirmed', 'mo']])\n",
    "(a.assign(lag_cfrm=a['confirmed'].shift(1))\n",
    "    .sort_values('day'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAG operation\n",
    "\n",
    "Here is the correct query showing the cases for the day before:\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS lag\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "Notice how the values in the LAG column match the value of the row diagonally above and to the left.\n",
    "\n",
    "name | DAY(whn) | confirmed | dbf\n",
    "------|---|------|----------\n",
    "Italy | 1 | **1694** | null\n",
    "Italy | 2 | 2036 | **1694**\n",
    "Italy | 3 | 2502 | 2036\n",
    "Italy | 4 | 3089 | 2502\n",
    "Italy | 5 | **3858** | 3089\n",
    "Italy | 6 | 4636 | **3858**\n",
    "Italy | 7 | 5883 | 4636\n",
    "Italy | 8 | 7375 | 5883\n",
    "Italy | 9 | 9172 | 7375\n",
    "Italy | 10 | 10149 | 9172\n",
    "... | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Number of new cases\n",
    "\n",
    "The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.\n",
    "\n",
    "**Show the number of new cases for each day, for Italy, for March.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>day</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7329</th>\n",
       "      <td>Italy</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7330</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2</td>\n",
       "      <td>342.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7331</th>\n",
       "      <td>Italy</td>\n",
       "      <td>3</td>\n",
       "      <td>466.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7332</th>\n",
       "      <td>Italy</td>\n",
       "      <td>4</td>\n",
       "      <td>587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7333</th>\n",
       "      <td>Italy</td>\n",
       "      <td>5</td>\n",
       "      <td>769.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7334</th>\n",
       "      <td>Italy</td>\n",
       "      <td>6</td>\n",
       "      <td>778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7335</th>\n",
       "      <td>Italy</td>\n",
       "      <td>7</td>\n",
       "      <td>1247.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7336</th>\n",
       "      <td>Italy</td>\n",
       "      <td>8</td>\n",
       "      <td>1492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7337</th>\n",
       "      <td>Italy</td>\n",
       "      <td>9</td>\n",
       "      <td>1797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7338</th>\n",
       "      <td>Italy</td>\n",
       "      <td>10</td>\n",
       "      <td>977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7339</th>\n",
       "      <td>Italy</td>\n",
       "      <td>11</td>\n",
       "      <td>2313.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7340</th>\n",
       "      <td>Italy</td>\n",
       "      <td>12</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7341</th>\n",
       "      <td>Italy</td>\n",
       "      <td>13</td>\n",
       "      <td>5198.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7342</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14</td>\n",
       "      <td>3497.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7343</th>\n",
       "      <td>Italy</td>\n",
       "      <td>15</td>\n",
       "      <td>3590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7344</th>\n",
       "      <td>Italy</td>\n",
       "      <td>16</td>\n",
       "      <td>3233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7345</th>\n",
       "      <td>Italy</td>\n",
       "      <td>17</td>\n",
       "      <td>3526.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7346</th>\n",
       "      <td>Italy</td>\n",
       "      <td>18</td>\n",
       "      <td>4207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7347</th>\n",
       "      <td>Italy</td>\n",
       "      <td>19</td>\n",
       "      <td>5322.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7348</th>\n",
       "      <td>Italy</td>\n",
       "      <td>20</td>\n",
       "      <td>5986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7349</th>\n",
       "      <td>Italy</td>\n",
       "      <td>21</td>\n",
       "      <td>6557.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7350</th>\n",
       "      <td>Italy</td>\n",
       "      <td>22</td>\n",
       "      <td>5560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7351</th>\n",
       "      <td>Italy</td>\n",
       "      <td>23</td>\n",
       "      <td>4789.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7352</th>\n",
       "      <td>Italy</td>\n",
       "      <td>24</td>\n",
       "      <td>5249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7353</th>\n",
       "      <td>Italy</td>\n",
       "      <td>25</td>\n",
       "      <td>5210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7354</th>\n",
       "      <td>Italy</td>\n",
       "      <td>26</td>\n",
       "      <td>6203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7355</th>\n",
       "      <td>Italy</td>\n",
       "      <td>27</td>\n",
       "      <td>5909.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7356</th>\n",
       "      <td>Italy</td>\n",
       "      <td>28</td>\n",
       "      <td>5974.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7357</th>\n",
       "      <td>Italy</td>\n",
       "      <td>29</td>\n",
       "      <td>5217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7358</th>\n",
       "      <td>Italy</td>\n",
       "      <td>30</td>\n",
       "      <td>4050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7359</th>\n",
       "      <td>Italy</td>\n",
       "      <td>31</td>\n",
       "      <td>4053.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       name  day     new\n",
       "7329  Italy    1     NaN\n",
       "7330  Italy    2   342.0\n",
       "7331  Italy    3   466.0\n",
       "7332  Italy    4   587.0\n",
       "7333  Italy    5   769.0\n",
       "7334  Italy    6   778.0\n",
       "7335  Italy    7  1247.0\n",
       "7336  Italy    8  1492.0\n",
       "7337  Italy    9  1797.0\n",
       "7338  Italy   10   977.0\n",
       "7339  Italy   11  2313.0\n",
       "7340  Italy   12     0.0\n",
       "7341  Italy   13  5198.0\n",
       "7342  Italy   14  3497.0\n",
       "7343  Italy   15  3590.0\n",
       "7344  Italy   16  3233.0\n",
       "7345  Italy   17  3526.0\n",
       "7346  Italy   18  4207.0\n",
       "7347  Italy   19  5322.0\n",
       "7348  Italy   20  5986.0\n",
       "7349  Italy   21  6557.0\n",
       "7350  Italy   22  5560.0\n",
       "7351  Italy   23  4789.0\n",
       "7352  Italy   24  5249.0\n",
       "7353  Italy   25  5210.0\n",
       "7354  Italy   26  6203.0\n",
       "7355  Italy   27  5909.0\n",
       "7356  Italy   28  5974.0\n",
       "7357  Italy   29  5217.0\n",
       "7358  Italy   30  4050.0\n",
       "7359  Italy   31  4053.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = covid.assign(day=covid['whn'].dt.day)[\n",
    "    (covid['name']=='Italy') & (covid['whn'].dt.month==3)]\n",
    "a.assign(new=a['confirmed']-a['confirmed'].shift(1))[['name', 'day', 'new']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Weekly changes\n",
    "\n",
    "The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.\n",
    "\n",
    "You can filter the data to view only Monday's figures **WHERE WEEKDAY(whn) = 0**.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7294</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7301</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7308</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7315</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7322</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-23</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7329</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7336</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-08</td>\n",
       "      <td>5681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7343</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>17372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7350</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>34391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7357</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>38551.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7364</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>31259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7371</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>27415.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>22609.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       name        whn      new\n",
       "7294  Italy 2020-01-26      NaN\n",
       "7301  Italy 2020-02-02      2.0\n",
       "7308  Italy 2020-02-09      1.0\n",
       "7315  Italy 2020-02-16      0.0\n",
       "7322  Italy 2020-02-23    152.0\n",
       "7329  Italy 2020-03-01   1539.0\n",
       "7336  Italy 2020-03-08   5681.0\n",
       "7343  Italy 2020-03-15  17372.0\n",
       "7350  Italy 2020-03-22  34391.0\n",
       "7357  Italy 2020-03-29  38551.0\n",
       "7364  Italy 2020-04-05  31259.0\n",
       "7371  Italy 2020-04-12  27415.0\n",
       "7378  Italy 2020-04-19  22609.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (covid[(covid['name']=='Italy') & (covid['whn'].dt.weekday==6)]).copy()\n",
    "(a.assign(new = a['confirmed'] - a['confirmed'].shift(1))\n",
    "        .loc[:, ['name', 'whn', 'new']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. LAG using a JOIN\n",
    "\n",
    "You can JOIN a table using DATE arithmetic. This will give different results if data is missing.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**\n",
    "\n",
    "In the sample query we JOIN this week tw with last week lw using the DATE_ADD function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-16</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-02-23</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-08</td>\n",
       "      <td>5681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-15</td>\n",
       "      <td>17372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-22</td>\n",
       "      <td>34391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>38551.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>31259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>27415.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>22609.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name        whn      new\n",
       "0   Italy 2020-01-26      NaN\n",
       "1   Italy 2020-02-02      2.0\n",
       "2   Italy 2020-02-09      1.0\n",
       "3   Italy 2020-02-16      0.0\n",
       "4   Italy 2020-02-23    152.0\n",
       "5   Italy 2020-03-01   1539.0\n",
       "6   Italy 2020-03-08   5681.0\n",
       "7   Italy 2020-03-15  17372.0\n",
       "8   Italy 2020-03-22  34391.0\n",
       "9   Italy 2020-03-29  38551.0\n",
       "10  Italy 2020-04-05  31259.0\n",
       "11  Italy 2020-04-12  27415.0\n",
       "12  Italy 2020-04-19  22609.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SELECT tw.name, TO_CHAR(tw.whn,'yyyy-mm-dd'), \n",
    "#   tw.confirmed - lw.confirmed AS new\n",
    "#     FROM covid tw LEFT JOIN covid lw ON \n",
    "#     lw.whn + INTERVAL '1w' = tw.whn AND tw.name=lw.name\n",
    "#     WHERE tw.name = 'Italy' AND EXTRACT(DOW FROM tw.whn)=0\n",
    "#     ORDER BY tw.whn;\n",
    "import datetime\n",
    "a = (covid[(covid['name']=='Italy') & (covid['whn'].dt.weekday==6)]).copy()\n",
    "b = (a.merge(a.assign(whn=covid['whn'] + datetime.timedelta(weeks=1)), \n",
    "        how='left', left_on=['whn', 'name'], right_on=['whn', 'name']))\n",
    "b.assign(new=b['confirmed_x']-b['confirmed_y'])[['name', 'whn', 'new']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. RANK()\n",
    "\n",
    "The query shown shows the number of confirmed cases together with the world ranking for cases.\n",
    "\n",
    "United States has the highest number, Spain is number 2...\n",
    "\n",
    "Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.\n",
    "\n",
    "**Include the ranking for the number of deaths in the table. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>rc1</th>\n",
       "      <th>deaths</th>\n",
       "      <th>rc2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15479</th>\n",
       "      <td>United States</td>\n",
       "      <td>784326</td>\n",
       "      <td>1.0</td>\n",
       "      <td>42094</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13769</th>\n",
       "      <td>Spain</td>\n",
       "      <td>200210</td>\n",
       "      <td>21.0</td>\n",
       "      <td>20852</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>Italy</td>\n",
       "      <td>181228</td>\n",
       "      <td>27.0</td>\n",
       "      <td>24114</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5399</th>\n",
       "      <td>France</td>\n",
       "      <td>156480</td>\n",
       "      <td>42.0</td>\n",
       "      <td>20292</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5759</th>\n",
       "      <td>Germany</td>\n",
       "      <td>147065</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4862</td>\n",
       "      <td>119.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15389</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>125856</td>\n",
       "      <td>73.0</td>\n",
       "      <td>16550</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15029</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>90980</td>\n",
       "      <td>107.0</td>\n",
       "      <td>2140</td>\n",
       "      <td>263.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>China</td>\n",
       "      <td>83817</td>\n",
       "      <td>116.0</td>\n",
       "      <td>4636</td>\n",
       "      <td>126.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7019</th>\n",
       "      <td>Iran</td>\n",
       "      <td>83505</td>\n",
       "      <td>120.0</td>\n",
       "      <td>5209</td>\n",
       "      <td>111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12059</th>\n",
       "      <td>Russia</td>\n",
       "      <td>47121</td>\n",
       "      <td>247.0</td>\n",
       "      <td>405</td>\n",
       "      <td>497.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>40743</td>\n",
       "      <td>262.0</td>\n",
       "      <td>2587</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1529</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>39983</td>\n",
       "      <td>264.0</td>\n",
       "      <td>5828</td>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2879</th>\n",
       "      <td>Canada</td>\n",
       "      <td>37657</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1725</td>\n",
       "      <td>287.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10439</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>33588</td>\n",
       "      <td>290.0</td>\n",
       "      <td>3764</td>\n",
       "      <td>149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11789</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>20863</td>\n",
       "      <td>376.0</td>\n",
       "      <td>735</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6839</th>\n",
       "      <td>India</td>\n",
       "      <td>18539</td>\n",
       "      <td>401.0</td>\n",
       "      <td>592</td>\n",
       "      <td>419.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11519</th>\n",
       "      <td>Peru</td>\n",
       "      <td>16325</td>\n",
       "      <td>424.0</td>\n",
       "      <td>445</td>\n",
       "      <td>472.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14129</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>14777</td>\n",
       "      <td>445.0</td>\n",
       "      <td>1580</td>\n",
       "      <td>296.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7559</th>\n",
       "      <td>Japan</td>\n",
       "      <td>10797</td>\n",
       "      <td>535.5</td>\n",
       "      <td>236</td>\n",
       "      <td>635.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13589</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>10674</td>\n",
       "      <td>539.0</td>\n",
       "      <td>236</td>\n",
       "      <td>635.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3149</th>\n",
       "      <td>Chile</td>\n",
       "      <td>10507</td>\n",
       "      <td>550.0</td>\n",
       "      <td>139</td>\n",
       "      <td>820.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12689</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>10484</td>\n",
       "      <td>551.0</td>\n",
       "      <td>103</td>\n",
       "      <td>945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4499</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>10128</td>\n",
       "      <td>570.0</td>\n",
       "      <td>507</td>\n",
       "      <td>446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11699</th>\n",
       "      <td>Poland</td>\n",
       "      <td>9593</td>\n",
       "      <td>591.0</td>\n",
       "      <td>380</td>\n",
       "      <td>511.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11969</th>\n",
       "      <td>Romania</td>\n",
       "      <td>8936</td>\n",
       "      <td>618.0</td>\n",
       "      <td>478</td>\n",
       "      <td>457.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11159</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>8418</td>\n",
       "      <td>637.5</td>\n",
       "      <td>176</td>\n",
       "      <td>739.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9539</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>8261</td>\n",
       "      <td>646.0</td>\n",
       "      <td>686</td>\n",
       "      <td>402.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4049</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>6900</td>\n",
       "      <td>721.0</td>\n",
       "      <td>194</td>\n",
       "      <td>697.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6929</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>6760</td>\n",
       "      <td>731.0</td>\n",
       "      <td>590</td>\n",
       "      <td>422.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>809</th>\n",
       "      <td>Australia</td>\n",
       "      <td>6547</td>\n",
       "      <td>753.0</td>\n",
       "      <td>67</td>\n",
       "      <td>1153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11609</th>\n",
       "      <td>Philippines</td>\n",
       "      <td>6459</td>\n",
       "      <td>760.0</td>\n",
       "      <td>428</td>\n",
       "      <td>483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15209</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>5710</td>\n",
       "      <td>834.0</td>\n",
       "      <td>151</td>\n",
       "      <td>789.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8999</th>\n",
       "      <td>Malaysia</td>\n",
       "      <td>5425</td>\n",
       "      <td>860.0</td>\n",
       "      <td>89</td>\n",
       "      <td>1015.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4409</th>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>4964</td>\n",
       "      <td>902.0</td>\n",
       "      <td>235</td>\n",
       "      <td>638.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3329</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>3977</td>\n",
       "      <td>1006.0</td>\n",
       "      <td>189</td>\n",
       "      <td>708.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4589</th>\n",
       "      <td>Egypt</td>\n",
       "      <td>3333</td>\n",
       "      <td>1091.5</td>\n",
       "      <td>250</td>\n",
       "      <td>617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13499</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>3300</td>\n",
       "      <td>1095.0</td>\n",
       "      <td>58</td>\n",
       "      <td>1225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9989</th>\n",
       "      <td>Morocco</td>\n",
       "      <td>3046</td>\n",
       "      <td>1136.0</td>\n",
       "      <td>143</td>\n",
       "      <td>809.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1259</th>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>2948</td>\n",
       "      <td>1158.0</td>\n",
       "      <td>101</td>\n",
       "      <td>954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2941</td>\n",
       "      <td>1160.0</td>\n",
       "      <td>136</td>\n",
       "      <td>831.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14579</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>2792</td>\n",
       "      <td>1184.0</td>\n",
       "      <td>47</td>\n",
       "      <td>1330.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2718</td>\n",
       "      <td>1206.0</td>\n",
       "      <td>384</td>\n",
       "      <td>509.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5939</th>\n",
       "      <td>Greece</td>\n",
       "      <td>2245</td>\n",
       "      <td>1316.5</td>\n",
       "      <td>116</td>\n",
       "      <td>898.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7739</th>\n",
       "      <td>Kazakhstan</td>\n",
       "      <td>1852</td>\n",
       "      <td>1436.0</td>\n",
       "      <td>19</td>\n",
       "      <td>1914.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15659</th>\n",
       "      <td>Uzbekistan</td>\n",
       "      <td>1627</td>\n",
       "      <td>1524.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7109</th>\n",
       "      <td>Iraq</td>\n",
       "      <td>1574</td>\n",
       "      <td>1544.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1052.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>989</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>1436</td>\n",
       "      <td>1616.0</td>\n",
       "      <td>19</td>\n",
       "      <td>1914.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2789</th>\n",
       "      <td>Cameroon</td>\n",
       "      <td>1163</td>\n",
       "      <td>1799.0</td>\n",
       "      <td>42</td>\n",
       "      <td>1396.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3869</th>\n",
       "      <td>Cuba</td>\n",
       "      <td>1087</td>\n",
       "      <td>1850.0</td>\n",
       "      <td>36</td>\n",
       "      <td>1489.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5849</th>\n",
       "      <td>Ghana</td>\n",
       "      <td>1042</td>\n",
       "      <td>1885.5</td>\n",
       "      <td>9</td>\n",
       "      <td>2351.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1026</td>\n",
       "      <td>1899.5</td>\n",
       "      <td>36</td>\n",
       "      <td>1489.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14939</th>\n",
       "      <td>Tunisia</td>\n",
       "      <td>884</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>38</td>\n",
       "      <td>1453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3689</th>\n",
       "      <td>Côte d'Ivoire</td>\n",
       "      <td>847</td>\n",
       "      <td>2044.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2351.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10799</th>\n",
       "      <td>Nigeria</td>\n",
       "      <td>665</td>\n",
       "      <td>2243.0</td>\n",
       "      <td>22</td>\n",
       "      <td>1806.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10709</th>\n",
       "      <td>Niger</td>\n",
       "      <td>648</td>\n",
       "      <td>2280.0</td>\n",
       "      <td>20</td>\n",
       "      <td>1878.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6209</th>\n",
       "      <td>Guinea</td>\n",
       "      <td>622</td>\n",
       "      <td>2326.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429</th>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>581</td>\n",
       "      <td>2379.0</td>\n",
       "      <td>38</td>\n",
       "      <td>1453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1889</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>564</td>\n",
       "      <td>2410.5</td>\n",
       "      <td>33</td>\n",
       "      <td>1558.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7649</th>\n",
       "      <td>Jordan</td>\n",
       "      <td>425</td>\n",
       "      <td>2670.5</td>\n",
       "      <td>7</td>\n",
       "      <td>2581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14399</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>422</td>\n",
       "      <td>2680.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2735.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12779</th>\n",
       "      <td>Senegal</td>\n",
       "      <td>377</td>\n",
       "      <td>2799.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3419</th>\n",
       "      <td>Congo, Democratic Republic of</td>\n",
       "      <td>332</td>\n",
       "      <td>2907.0</td>\n",
       "      <td>25</td>\n",
       "      <td>1723.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13859</th>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>304</td>\n",
       "      <td>2994.0</td>\n",
       "      <td>7</td>\n",
       "      <td>2581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6119</th>\n",
       "      <td>Guatemala</td>\n",
       "      <td>289</td>\n",
       "      <td>3032.5</td>\n",
       "      <td>7</td>\n",
       "      <td>2581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7829</th>\n",
       "      <td>Kenya</td>\n",
       "      <td>281</td>\n",
       "      <td>3051.5</td>\n",
       "      <td>14</td>\n",
       "      <td>2078.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15929</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>268</td>\n",
       "      <td>3096.5</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15839</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>256</td>\n",
       "      <td>3153.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2351.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14489</th>\n",
       "      <td>Tanzania</td>\n",
       "      <td>254</td>\n",
       "      <td>3161.0</td>\n",
       "      <td>10</td>\n",
       "      <td>2271.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9179</th>\n",
       "      <td>Mali</td>\n",
       "      <td>246</td>\n",
       "      <td>3190.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2078.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13409</th>\n",
       "      <td>Somalia</td>\n",
       "      <td>237</td>\n",
       "      <td>3229.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12149</th>\n",
       "      <td>Rwanda</td>\n",
       "      <td>147</td>\n",
       "      <td>3645.5</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2699</th>\n",
       "      <td>Cambodia</td>\n",
       "      <td>122</td>\n",
       "      <td>3814.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8819</th>\n",
       "      <td>Madagascar</td>\n",
       "      <td>121</td>\n",
       "      <td>3822.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10169</th>\n",
       "      <td>Myanmar</td>\n",
       "      <td>119</td>\n",
       "      <td>3838.5</td>\n",
       "      <td>5</td>\n",
       "      <td>2922.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>111</td>\n",
       "      <td>3911.5</td>\n",
       "      <td>3</td>\n",
       "      <td>3353.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13949</th>\n",
       "      <td>Sudan</td>\n",
       "      <td>107</td>\n",
       "      <td>3963.0</td>\n",
       "      <td>12</td>\n",
       "      <td>2158.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16109</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>65</td>\n",
       "      <td>4409.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3353.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6479</th>\n",
       "      <td>Haiti</td>\n",
       "      <td>57</td>\n",
       "      <td>4526.5</td>\n",
       "      <td>3</td>\n",
       "      <td>3353.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15119</th>\n",
       "      <td>Uganda</td>\n",
       "      <td>56</td>\n",
       "      <td>4545.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709</th>\n",
       "      <td>Benin</td>\n",
       "      <td>54</td>\n",
       "      <td>4583.5</td>\n",
       "      <td>1</td>\n",
       "      <td>4324.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14309</th>\n",
       "      <td>Syria</td>\n",
       "      <td>39</td>\n",
       "      <td>4898.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3353.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10079</th>\n",
       "      <td>Mozambique</td>\n",
       "      <td>39</td>\n",
       "      <td>4898.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3059</th>\n",
       "      <td>Chad</td>\n",
       "      <td>33</td>\n",
       "      <td>5058.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10349</th>\n",
       "      <td>Nepal</td>\n",
       "      <td>31</td>\n",
       "      <td>5118.5</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16199</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>25</td>\n",
       "      <td>5299.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3353.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>Angola</td>\n",
       "      <td>24</td>\n",
       "      <td>5337.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3697.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8909</th>\n",
       "      <td>Malawi</td>\n",
       "      <td>17</td>\n",
       "      <td>5699.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3697.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2519</th>\n",
       "      <td>Burundi</td>\n",
       "      <td>5</td>\n",
       "      <td>7090.5</td>\n",
       "      <td>1</td>\n",
       "      <td>4324.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13679</th>\n",
       "      <td>South Sudan</td>\n",
       "      <td>4</td>\n",
       "      <td>7258.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16019</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>1</td>\n",
       "      <td>8326.5</td>\n",
       "      <td>0</td>\n",
       "      <td>10481.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name  confirmed     rc1  deaths      rc2\n",
       "15479                  United States     784326     1.0   42094      1.0\n",
       "13769                          Spain     200210    21.0   20852     17.0\n",
       "7379                           Italy     181228    27.0   24114      8.0\n",
       "5399                          France     156480    42.0   20292     21.0\n",
       "5759                         Germany     147065    52.0    4862    119.0\n",
       "15389                 United Kingdom     125856    73.0   16550     42.0\n",
       "15029                         Turkey      90980   107.0    2140    263.0\n",
       "3239                           China      83817   116.0    4636    126.5\n",
       "7019                            Iran      83505   120.0    5209    111.0\n",
       "12059                         Russia      47121   247.0     405    497.5\n",
       "2159                          Brazil      40743   262.0    2587    240.0\n",
       "1529                         Belgium      39983   264.0    5828    105.0\n",
       "2879                          Canada      37657   273.0    1725    287.0\n",
       "10439                    Netherlands      33588   290.0    3764    149.0\n",
       "11789                       Portugal      20863   376.0     735    390.0\n",
       "6839                           India      18539   401.0     592    419.0\n",
       "11519                           Peru      16325   424.0     445    472.5\n",
       "14129                         Sweden      14777   445.0    1580    296.0\n",
       "7559                           Japan      10797   535.5     236    635.0\n",
       "13589                    South Korea      10674   539.0     236    635.0\n",
       "3149                           Chile      10507   550.0     139    820.0\n",
       "12689                   Saudi Arabia      10484   551.0     103    945.0\n",
       "4499                         Ecuador      10128   570.0     507    446.0\n",
       "11699                         Poland       9593   591.0     380    511.5\n",
       "11969                        Romania       8936   618.0     478    457.0\n",
       "11159                       Pakistan       8418   637.5     176    739.5\n",
       "9539                          Mexico       8261   646.0     686    402.5\n",
       "4049                  Czech Republic       6900   721.0     194    697.0\n",
       "6929                       Indonesia       6760   731.0     590    422.0\n",
       "809                        Australia       6547   753.0      67   1153.0\n",
       "11609                    Philippines       6459   760.0     428    483.0\n",
       "15209                        Ukraine       5710   834.0     151    789.0\n",
       "8999                        Malaysia       5425   860.0      89   1015.0\n",
       "4409              Dominican Republic       4964   902.0     235    638.0\n",
       "3329                        Colombia       3977  1006.0     189    708.0\n",
       "4589                           Egypt       3333  1091.5     250    617.0\n",
       "13499                   South Africa       3300  1095.0      58   1225.0\n",
       "9989                         Morocco       3046  1136.0     143    809.5\n",
       "1259                      Bangladesh       2948  1158.0     101    954.0\n",
       "629                        Argentina       2941  1160.0     136    831.0\n",
       "14579                       Thailand       2792  1184.0      47   1330.5\n",
       "269                          Algeria       2718  1206.0     384    509.5\n",
       "5939                          Greece       2245  1316.5     116    898.0\n",
       "7739                      Kazakhstan       1852  1436.0      19   1914.5\n",
       "15659                     Uzbekistan       1627  1524.0       5   2922.0\n",
       "7109                            Iraq       1574  1544.0      82   1052.5\n",
       "989                       Azerbaijan       1436  1616.0      19   1914.5\n",
       "2789                        Cameroon       1163  1799.0      42   1396.0\n",
       "3869                            Cuba       1087  1850.0      36   1489.0\n",
       "5849                           Ghana       1042  1885.5       9   2351.5\n",
       "89                       Afghanistan       1026  1899.5      36   1489.0\n",
       "14939                        Tunisia        884  2008.0      38   1453.0\n",
       "3689                   Côte d'Ivoire        847  2044.0       9   2351.5\n",
       "10799                        Nigeria        665  2243.0      22   1806.0\n",
       "10709                          Niger        648  2280.0      20   1878.5\n",
       "6209                          Guinea        622  2326.0       5   2922.0\n",
       "2429                    Burkina Faso        581  2379.0      38   1453.0\n",
       "1889                         Bolivia        564  2410.5      33   1558.0\n",
       "7649                          Jordan        425  2670.5       7   2581.0\n",
       "14399                         Taiwan        422  2680.0       6   2735.0\n",
       "12779                        Senegal        377  2799.0       5   2922.0\n",
       "3419   Congo, Democratic Republic of        332  2907.0      25   1723.5\n",
       "13859                      Sri Lanka        304  2994.0       7   2581.0\n",
       "6119                       Guatemala        289  3032.5       7   2581.0\n",
       "7829                           Kenya        281  3051.5      14   2078.0\n",
       "15929                        Vietnam        268  3096.5       0  10481.5\n",
       "15839                      Venezuela        256  3153.0       9   2351.5\n",
       "14489                       Tanzania        254  3161.0      10   2271.5\n",
       "9179                            Mali        246  3190.0      14   2078.0\n",
       "13409                        Somalia        237  3229.0       8   2455.0\n",
       "12149                         Rwanda        147  3645.5       0  10481.5\n",
       "2699                        Cambodia        122  3814.0       0  10481.5\n",
       "8819                      Madagascar        121  3822.0       0  10481.5\n",
       "10169                        Myanmar        119  3838.5       5   2922.0\n",
       "5129                        Ethiopia        111  3911.5       3   3353.5\n",
       "13949                          Sudan        107  3963.0      12   2158.5\n",
       "16109                         Zambia         65  4409.0       3   3353.5\n",
       "6479                           Haiti         57  4526.5       3   3353.5\n",
       "15119                         Uganda         56  4545.0       0  10481.5\n",
       "1709                           Benin         54  4583.5       1   4324.5\n",
       "14309                          Syria         39  4898.0       3   3353.5\n",
       "10079                     Mozambique         39  4898.0       0  10481.5\n",
       "3059                            Chad         33  5058.0       0  10481.5\n",
       "10349                          Nepal         31  5118.5       0  10481.5\n",
       "16199                       Zimbabwe         25  5299.0       3   3353.5\n",
       "449                           Angola         24  5337.0       2   3697.0\n",
       "8909                          Malawi         17  5699.0       2   3697.0\n",
       "2519                         Burundi          5  7090.5       1   4324.5\n",
       "13679                    South Sudan          4  7258.0       0  10481.5\n",
       "16019                          Yemen          1  8326.5       0  10481.5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = covid.merge(world[['name', 'population']], left_on='name', right_on='name')\n",
    "(a.assign(rc1=a['confirmed'].rank(ascending=False),\n",
    "          rc2=a['deaths'].rank(ascending=False))\n",
    "    .loc[(a['whn']=='2020-04-20') & (a['population']>=1e7),\n",
    "        ['name', 'confirmed', 'rc1', 'deaths', 'rc2']]\n",
    "    .sort_values('confirmed', ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Infection rate\n",
    "\n",
    "The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).\n",
    "\n",
    "**Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>r_inf</th>\n",
       "      <th>rc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3239</th>\n",
       "      <td>China</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6839</th>\n",
       "      <td>India</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15479</th>\n",
       "      <td>United States</td>\n",
       "      <td>238.0</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6929</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11159</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2159</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>19.0</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10799</th>\n",
       "      <td>Nigeria</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1259</th>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12059</th>\n",
       "      <td>Russia</td>\n",
       "      <td>32.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9539</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>7.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7559</th>\n",
       "      <td>Japan</td>\n",
       "      <td>9.0</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11609</th>\n",
       "      <td>Philippines</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4589</th>\n",
       "      <td>Egypt</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15929</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7019</th>\n",
       "      <td>Iran</td>\n",
       "      <td>100.0</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15029</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>109.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5759</th>\n",
       "      <td>Germany</td>\n",
       "      <td>177.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3419</th>\n",
       "      <td>Congo, Democratic Republic of</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5399</th>\n",
       "      <td>France</td>\n",
       "      <td>233.0</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14579</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15389</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>189.0</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>Italy</td>\n",
       "      <td>301.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13499</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14489</th>\n",
       "      <td>Tanzania</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10169</th>\n",
       "      <td>Myanmar</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13589</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>21.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3329</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>8.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7829</th>\n",
       "      <td>Kenya</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13769</th>\n",
       "      <td>Spain</td>\n",
       "      <td>425.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>7.0</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13949</th>\n",
       "      <td>Sudan</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15209</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>14.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15119</th>\n",
       "      <td>Uganda</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7109</th>\n",
       "      <td>Iraq</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11699</th>\n",
       "      <td>Poland</td>\n",
       "      <td>25.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2879</th>\n",
       "      <td>Canada</td>\n",
       "      <td>99.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9989</th>\n",
       "      <td>Morocco</td>\n",
       "      <td>8.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12689</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>31.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15659</th>\n",
       "      <td>Uzbekistan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8999</th>\n",
       "      <td>Malaysia</td>\n",
       "      <td>17.0</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15839</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11519</th>\n",
       "      <td>Peru</td>\n",
       "      <td>51.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>Angola</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5849</th>\n",
       "      <td>Ghana</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10079</th>\n",
       "      <td>Mozambique</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10349</th>\n",
       "      <td>Nepal</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16019</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2789</th>\n",
       "      <td>Cameroon</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8819</th>\n",
       "      <td>Madagascar</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>809</th>\n",
       "      <td>Australia</td>\n",
       "      <td>25.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3689</th>\n",
       "      <td>Côte d'Ivoire</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14399</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10709</th>\n",
       "      <td>Niger</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13859</th>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2429</th>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>3.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9179</th>\n",
       "      <td>Mali</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11969</th>\n",
       "      <td>Romania</td>\n",
       "      <td>46.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8909</th>\n",
       "      <td>Malawi</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3149</th>\n",
       "      <td>Chile</td>\n",
       "      <td>55.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7739</th>\n",
       "      <td>Kazakhstan</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16109</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14309</th>\n",
       "      <td>Syria</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4499</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>58.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10439</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>192.0</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6119</th>\n",
       "      <td>Guatemala</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3059</th>\n",
       "      <td>Chad</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12779</th>\n",
       "      <td>Senegal</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13409</th>\n",
       "      <td>Somalia</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2699</th>\n",
       "      <td>Cambodia</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16199</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13679</th>\n",
       "      <td>South Sudan</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12149</th>\n",
       "      <td>Rwanda</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6209</th>\n",
       "      <td>Guinea</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709</th>\n",
       "      <td>Benin</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14939</th>\n",
       "      <td>Tunisia</td>\n",
       "      <td>8.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6479</th>\n",
       "      <td>Haiti</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1529</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>347.0</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1889</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3869</th>\n",
       "      <td>Cuba</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2519</th>\n",
       "      <td>Burundi</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5939</th>\n",
       "      <td>Greece</td>\n",
       "      <td>21.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4049</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>65.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7649</th>\n",
       "      <td>Jordan</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4409</th>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>48.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14129</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>143.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11789</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>203.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>989</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>14.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name  r_inf    rc\n",
       "3239                           China    6.0  50.0\n",
       "6839                           India    1.0  23.0\n",
       "15479                  United States  238.0  87.0\n",
       "6929                       Indonesia    3.0  35.0\n",
       "11159                       Pakistan    4.0  41.0\n",
       "2159                          Brazil   19.0  65.0\n",
       "10799                        Nigeria    0.0   1.0\n",
       "1259                      Bangladesh    2.0  31.0\n",
       "12059                         Russia   32.0  71.0\n",
       "9539                          Mexico    7.0  54.0\n",
       "7559                           Japan    9.0  59.0\n",
       "11609                    Philippines    6.0  50.0\n",
       "4589                           Egypt    3.0  35.0\n",
       "5129                        Ethiopia    0.0   1.0\n",
       "15929                        Vietnam    0.0   1.0\n",
       "7019                            Iran  100.0  79.0\n",
       "15029                         Turkey  109.0  80.0\n",
       "5759                         Germany  177.0  82.0\n",
       "3419   Congo, Democratic Republic of    0.0   1.0\n",
       "5399                          France  233.0  86.0\n",
       "14579                       Thailand    4.0  41.0\n",
       "15389                 United Kingdom  189.0  83.0\n",
       "7379                           Italy  301.0  88.0\n",
       "13499                   South Africa    6.0  50.0\n",
       "14489                       Tanzania    0.0   1.0\n",
       "10169                        Myanmar    0.0   1.0\n",
       "13589                    South Korea   21.0  66.0\n",
       "3329                        Colombia    8.0  56.0\n",
       "7829                           Kenya    1.0  23.0\n",
       "13769                          Spain  425.0  90.0\n",
       "629                        Argentina    7.0  54.0\n",
       "269                          Algeria    6.0  50.0\n",
       "13949                          Sudan    0.0   1.0\n",
       "15209                        Ukraine   14.0  62.0\n",
       "15119                         Uganda    0.0   1.0\n",
       "7109                            Iraq    4.0  41.0\n",
       "11699                         Poland   25.0  68.0\n",
       "2879                          Canada   99.0  78.0\n",
       "9989                         Morocco    8.0  56.0\n",
       "12689                   Saudi Arabia   31.0  70.0\n",
       "15659                     Uzbekistan    5.0  47.0\n",
       "8999                        Malaysia   17.0  64.0\n",
       "89                       Afghanistan    3.0  35.0\n",
       "15839                      Venezuela    1.0  23.0\n",
       "11519                           Peru   51.0  74.0\n",
       "449                           Angola    0.0   1.0\n",
       "5849                           Ghana    3.0  35.0\n",
       "10079                     Mozambique    0.0   1.0\n",
       "10349                          Nepal    0.0   1.0\n",
       "16019                          Yemen    0.0   1.0\n",
       "2789                        Cameroon    4.0  41.0\n",
       "8819                      Madagascar    0.0   1.0\n",
       "809                        Australia   25.0  68.0\n",
       "3689                   Côte d'Ivoire    4.0  41.0\n",
       "14399                         Taiwan    2.0  31.0\n",
       "10709                          Niger    3.0  35.0\n",
       "13859                      Sri Lanka    1.0  23.0\n",
       "2429                    Burkina Faso    3.0  35.0\n",
       "9179                            Mali    1.0  23.0\n",
       "11969                        Romania   46.0  72.0\n",
       "8909                          Malawi    0.0   1.0\n",
       "3149                           Chile   55.0  75.0\n",
       "7739                      Kazakhstan   10.0  60.0\n",
       "16109                         Zambia    0.0   1.0\n",
       "14309                          Syria    0.0   1.0\n",
       "4499                         Ecuador   58.0  76.0\n",
       "10439                    Netherlands  192.0  84.0\n",
       "6119                       Guatemala    2.0  31.0\n",
       "3059                            Chad    0.0   1.0\n",
       "12779                        Senegal    2.0  31.0\n",
       "13409                        Somalia    1.0  23.0\n",
       "2699                        Cambodia    1.0  23.0\n",
       "16199                       Zimbabwe    0.0   1.0\n",
       "13679                    South Sudan    0.0   1.0\n",
       "12149                         Rwanda    1.0  23.0\n",
       "6209                          Guinea    5.0  47.0\n",
       "1709                           Benin    0.0   1.0\n",
       "14939                        Tunisia    8.0  56.0\n",
       "6479                           Haiti    0.0   1.0\n",
       "1529                         Belgium  347.0  89.0\n",
       "1889                         Bolivia    5.0  47.0\n",
       "3869                            Cuba   10.0  60.0\n",
       "2519                         Burundi    0.0   1.0\n",
       "5939                          Greece   21.0  66.0\n",
       "4049                  Czech Republic   65.0  77.0\n",
       "7649                          Jordan    4.0  41.0\n",
       "4409              Dominican Republic   48.0  73.0\n",
       "14129                         Sweden  143.0  81.0\n",
       "11789                       Portugal  203.0  85.0\n",
       "989                       Azerbaijan   14.0  62.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #6\n",
    "b = (a[(a['whn']=='2020-04-20') & (a['population']>1e7)]).copy()\n",
    "b['r_inf'] = round(1e5*b['confirmed']/b['population'])\n",
    "(b.assign(rc=b['r_inf'].rank(method='min'))\n",
    "    .sort_values('population', ascending=False)\n",
    "    .loc[:, ['name', 'r_inf', 'rc']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Turning the corner\n",
    "\n",
    "For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>whn</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3172</th>\n",
       "      <td>China</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>15136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7349</th>\n",
       "      <td>Italy</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>6557.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14191</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>2020-03-23</td>\n",
       "      <td>1321.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7263</th>\n",
       "      <td>Israel</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>1131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13743</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2020-03-25</td>\n",
       "      <td>9630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>Austria</td>\n",
       "      <td>2020-03-26</td>\n",
       "      <td>1321.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5735</th>\n",
       "      <td>Germany</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>6933.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6998</th>\n",
       "      <td>Iran</td>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>3186.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2864</th>\n",
       "      <td>Canada</td>\n",
       "      <td>2020-04-05</td>\n",
       "      <td>2778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4489</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>2196.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7189</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10429</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1346.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>1516.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15379</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>8733.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15469</th>\n",
       "      <td>United States</td>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>33755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15020</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>2020-04-11</td>\n",
       "      <td>5138.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5391</th>\n",
       "      <td>France</td>\n",
       "      <td>2020-04-12</td>\n",
       "      <td>26849.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11512</th>\n",
       "      <td>Peru</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>2265.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1524</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>2454.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2156</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>3257.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7556</th>\n",
       "      <td>Japan</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>1161.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12687</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>1132.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6838</th>\n",
       "      <td>India</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>1893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12058</th>\n",
       "      <td>Russia</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>6060.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1439</th>\n",
       "      <td>Belarus</td>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>1485.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13139</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>1426.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name        whn      new\n",
       "3172            China 2020-02-13  15136.0\n",
       "7349            Italy 2020-03-21   6557.0\n",
       "14191     Switzerland 2020-03-23   1321.0\n",
       "7263           Israel 2020-03-25   1131.0\n",
       "13743           Spain 2020-03-25   9630.0\n",
       "874           Austria 2020-03-26   1321.0\n",
       "5735          Germany 2020-03-27   6933.0\n",
       "6998             Iran 2020-03-30   3186.0\n",
       "2864           Canada 2020-04-05   2778.0\n",
       "4489          Ecuador 2020-04-10   2196.0\n",
       "7189          Ireland 2020-04-10   1515.0\n",
       "10429     Netherlands 2020-04-10   1346.0\n",
       "11779        Portugal 2020-04-10   1516.0\n",
       "15379  United Kingdom 2020-04-10   8733.0\n",
       "15469   United States 2020-04-10  33755.0\n",
       "15020          Turkey 2020-04-11   5138.0\n",
       "5391           France 2020-04-12  26849.0\n",
       "11512            Peru 2020-04-13   2265.0\n",
       "1524          Belgium 2020-04-15   2454.0\n",
       "2156           Brazil 2020-04-17   3257.0\n",
       "7556            Japan 2020-04-17   1161.0\n",
       "12687    Saudi Arabia 2020-04-18   1132.0\n",
       "6838            India 2020-04-19   1893.0\n",
       "12058          Russia 2020-04-19   6060.0\n",
       "1439          Belarus 2020-04-20   1485.0\n",
       "13139       Singapore 2020-04-20   1426.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = covid.assign(new=(covid['confirmed']-covid.groupby('name')['confirmed']\n",
    "                      .shift(1)).fillna(0))\n",
    "a['rc'] = a.groupby('name')['new'].rank(ascending=False, method='max')\n",
    "(a.loc[(a['rc']==1) & (a['new']>1000), ['name', 'whn', 'new']]\n",
    "    .sort_values(['whn', 'name']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
